<?php

declare(strict_types=1);

namespace App\Command;

use Hyperf\Command\Command as HyperfCommand;
use Hyperf\Command\Annotation\Command;
use Psr\Container\ContainerInterface;

/**
 * @Command
 */
#[Command]
class LogSqlFetch extends HyperfCommand
{
    /**
     * @var ContainerInterface
     */
    protected $container;

    public function __construct(ContainerInterface $container)
    {
        $this->container = $container;

        parent::__construct('LogSqlFetch');
    }

    public function configure()
    {
        parent::configure();
        $this->setDescription('LogSqlFetch');
    }

    public function handle()
    {
        $logFile = '/Users/changjiang/Documents/code/hyperf/era_service_today_logs.txt';
        $outputFile = '/Users/changjiang/Documents/code/hyperf/sql_queries_' . date('Y_m_d_His') . '.sql';
        
        if (!file_exists($logFile)) {
            $this->error('Log file not found: ' . $logFile);
            return;
        }

        $logContent = file_get_contents($logFile);
        if ($logContent === false) {
            $this->error('Failed to read log file');
            return;
        }

        $queries = $this->extractQueriesFromLog($logContent);
        if (empty($queries)) {
            $this->warn('No SQL queries found in the log file');
            return;
        }

        // 准备输出内容
        $output = "-- SQL Queries extracted from logs\n";
        $output .= "-- Generated at: " . date('Y-m-d H:i:s') . "\n";
        $output .= "-- Total queries found: " . count($queries) . "\n\n";

        foreach ($queries as $index => $query) {
            $output .= "-- Query #" . ($index + 1) . "\n";
            $output .= $query . ";\n\n";
        }

        // 写入文件
        if (file_put_contents($outputFile, $output) !== false) {
            $this->info('Successfully extracted ' . count($queries) . ' SQL queries');
            $this->info('Results saved to: ' . $outputFile);
        } else {
            $this->error('Failed to write output file');
        }
    }

    private function extractQueriesFromLog(string $logContent): array
    {
        $pattern = '/Querying SQL: (.*?), Args: \[(.*?)\]/';
        preg_match_all($pattern, $logContent, $matches, PREG_SET_ORDER);

        $results = [];
        $uniqueQueries = []; // 用于存储已处理的SQL，实现去重
        
        foreach ($matches as $match) {
            $sql = trim($match[1]);
            $argsString = trim($match[2]);
            
            // Parse arguments string into array
            $args = [];
            if (!empty($argsString)) {
                // Split by space, since the log format uses space as separator
                $args = array_values(array_filter(explode(' ', $argsString)));
            }
            
            // Count the number of placeholders in SQL
            $placeholderCount = substr_count($sql, '?');
            
            if ($placeholderCount > 0) {
                // Replace each ? with the corresponding parameter
                $paramIndex = 0;
                $sql = preg_replace_callback('/\?/', function() use (&$paramIndex, $args) {
                    $value = $args[$paramIndex] ?? null;
                    $paramIndex++;
                    return $this->formatValue($value);
                }, $sql);
            }
            
            // 如果这个SQL还没有处理过，就添加到结果中
            if (!isset($uniqueQueries[$sql])) {
                $uniqueQueries[$sql] = true;
                $results[] = $sql;
            }
        }

        return $results;
    }

    private function formatValue($value): string
    {
        if ($value === null) {
            return 'NULL';
        }
        
        if (is_bool($value)) {
            return $value ? '1' : '0';
        }
        
        if (is_numeric($value)) {
            return $value;
        }
        
        return "'" . addslashes($value) . "'";
    }
}
